package com.kexun.dao.impl;

import com.kexun.dao.TeaDao;
import com.kexun.pojo.Course;
import com.kexun.pojo.ScoreStu;
import com.kexun.pojo.Student;
import com.kexun.pojo.Teacher;
import com.kexun.util.DruidUtil;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TeaDaoImpl implements TeaDao {
    JdbcTemplate t = new JdbcTemplate(DruidUtil.getDataSource());

    /**
     * 查找老师信息头
     *
     * @param userNum
     * @return
     */
    @Override
    public Teacher findInfo(String userNum) {
        String sql = "select * from teacher where tea_num =?";
        Teacher teacher = null;
        try {
            teacher = t.queryForObject(sql, new BeanPropertyRowMapper<>(Teacher.class), userNum);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        return teacher;

    }

    /**
     * 成绩管理--成绩信息查看
     *
     *
     * @param userNum
     * @param limit
     * @param page
     * @return
     */
    @Override
    public Map<String, Object> finStuScore(String userNum, int limit, int page) {

        int usernum = Integer.parseInt(userNum);

//        String sql = "select * from score order by  course_name asc limit ?,?";



        String sql1= "select * from score where course_name = (select tea_course from teacher where tea_num ='"+userNum+"') limit ?,?";

        String sqlCount = "select count(*) from score where course_name = (select tea_course from teacher where tea_num ='"+userNum+"')";

        List<ScoreStu> list = null;
        int i = 0;
        try {
            list = t.query(sql1, new BeanPropertyRowMapper<>(ScoreStu.class), (page - 1) * limit, limit);
            i = t.queryForObject(sqlCount,Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);


        return map;
    }

    /**
     * 添加学生成绩（成绩管理--添加学生成绩）
     *
     * @param scorestu
     * @return
     */
    @Override
    public int addScore(ScoreStu scorestu) {

        String sql = "insert into score values(default,'"+scorestu.getStuNum()+"','"+scorestu.getStuName()+"','"+scorestu.getStuClass()+"','"+scorestu.getCourseName()+"','"+scorestu.getScoreGrade()+"','"+scorestu.getMajor()+"')";

        int i = t.update(sql);

        return i;


    }

    /**
     * 课程管理——课程信息查看
     *
     * @param limit
     * @param page
     * @return
     */
    @Override
    public Map<String, Object> findCourseAll(int limit, int page) {
        String sql = "select * from course limit ?,?";
        String sqlCount = "select count(*) from course";
        List<Course> list = null;
        int i = 0;
        try {
           list = t.query(sql, new BeanPropertyRowMapper<>(Course.class), (page - 1) * limit, limit);
            i= t.queryForObject(sqlCount, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);

        return map;
    }

    /**
     * 课程管理——添加课程
     *
     * @param course
     * @return
     */
    @Override
    public int addCourse(Course course) {


        String sql ="insert into course values(default,'"+course.getCourseName()+"','"+course.getCourseCredit()+"','"+course.getCourseHours()+"','"+course.getCourseTeacher()+"','"+course.getCourseDate()+"')";
        int i = t.update(sql);
        return i;


    }

    /**
     * 修改个人信息（个人信息——修改个人信息）
     *
     * @param teacher
     * @return
     */
    @Override
    public int updateTea(Teacher teacher) {
        String sql = "update teacher set tea_name ='"+teacher.getTeaName()+"',tea_sex='"+teacher.getTeaSex()+"',tea_age='"+teacher.getTeaAge()+"',tea_course='"+teacher.getTeaCourse()+"',major='"+teacher.getMajor()+"',department='"+teacher.getDepartment()+"' where tea_num='"+teacher.getTeaNum()+"'";
        int i = t.update(sql);

        return i;

    }

    /**
     * 成绩管理--成绩信息查看——三个条件模糊查询
     *
     * @param stuNum
     * @param stuName
     * @param stuClass
     * @param limitStr
     * @param pageStr
     * @return
     */
    @Override
    public Map<String, Object> findScoreLike(String stuNum, String stuName, String stuClass, String limitStr, String pageStr,String teaNum) {

        String sql ="select * from score where stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%' and cast(stu_num as char)  like = '%"+stuNum+"%' and course_name = (select tea_course from teacher where tea_num = '"+teaNum+"') limit ?,?";

        String sql1 ="select * from score where stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%'and cast(stu_num as char)   like '%"+stuNum+"%' and course_name = (select tea_course from teacher where tea_num = '"+teaNum+"') limit ?,?";


        String sqlCount ="select count(*) from score where stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%' and cast(stu_num as char)  like = '%"+stuNum+"%' and course_name = (select tea_course from teacher where tea_num = '"+teaNum+"')";

        String sqlCount1 ="select count(*) from score where stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%'and cast(stu_num as char)   like '%"+stuNum+"%' and course_name = (select tea_course from teacher where tea_num = '"+teaNum+"')";






/*
        select * from score where stu_name like '%王%' and stu_class like '%%' and cast(stu_num as char)  like = '%%' and course_name = (select tea_course from teacher where tea_num = '1123') limit ?,?
        select count(*) from score where stu_name like '%王%' and stu_class like '%%' and cast(stu_num as char)  like = '%%' and course_name = (select tea_course from teacher where tea_num = '1123')


        select * from score where stu_name like '%王%' and stu_class like '%%' and cast(stu_num as char)  like = '%%' and course_name = (select tea_course from teacher where tea_num = '1123') limit ?,?
select count(*) from score where stu_name like '%王%' and stu_class like '%%' and cast(stu_num as char)  like = '%%' and course_name = (select tea_course from teacher where tea_num = '1123')


        */














        int limit = Integer.parseInt(limitStr);
        int page = Integer.parseInt(pageStr);


        List<ScoreStu> list =null;
        int i = 0 ;
        try {
           list = t.query(sql1, new BeanPropertyRowMapper<>(ScoreStu.class), (page - 1) * limit, limit);
           i = t.queryForObject(sqlCount1, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);


        return map;


    }

    /**
     * 查询学生信息
     *
     * @return
     */
    @Override
    public Map<String, Object> findStudent(String limitStr,String pageStr) {

        int limit = Integer.parseInt(limitStr);
        int page = Integer.parseInt(pageStr);



        String sql ="select * from student limit ?,?";

        String sqlCount = "select count(*) from student";

        List<Student> list = null;
        int i = 0 ;
        try {
             list = t.query(sql, new BeanPropertyRowMapper<>(Student.class), (page - 1) * limit, limit);
            i = t.queryForObject(sqlCount, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }


        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);

        return map;

    }

    /**
     * 删除学生信息
     *
     * @param stuNum
     * @return
     */
    @Override
    public int delStu(String stuNum) {
        System.out.println("进到dao层");
        String sql ="delete from student where stu_Num = '"+stuNum+"'";
        System.out.println(sql);
        int i = t.update(sql);
        return i;


    }


}

